﻿namespace KeppelLand.Controllers
{
    using KeppelLand.Entities;
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Web;


    /// <summary>
    /// Summary description for B2EController
    /// </summary>
    public class B2EController
    {
        public B2EController()
        {
        }

        public static bool IsHR(string domainName)
        {
            bool isHR = false;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM B2E where Section = 'HR' AND AccessLevel = 'HRA' AND EmployeeMaster = @EmployeeMaster;";
                cmd.Parameters.AddWithValue("@EmployeeMaster", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));

                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    isHR = reader.HasRows;
                }
            }

            return isHR;
        }

        public static bool IsSecretary(string domainName)
        {
            bool isSecretary = false;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT AccessLevel FROM B2E WHERE EmployeeMaster = @EmployeeMaster;";
                cmd.Parameters.AddWithValue("@EmployeeMaster", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));

                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        if (reader.GetString(0).Equals("SEC", StringComparison.InvariantCultureIgnoreCase))
                            isSecretary = true;
                    }
                }
            }

            //if (System.Web.HttpContext.Current.User.Identity.Name.Equals("KEPPELLAND\\angpp", StringComparison.InvariantCultureIgnoreCase)  ||
            //    System.Web.HttpContext.Current.User.Identity.Name.Equals("KLL\\angeline.ng", StringComparison.InvariantCultureIgnoreCase)   || 
            //    System.Web.HttpContext.Current.User.Identity.Name.Equals("KLL\\zhixian.ong", StringComparison.InvariantCultureIgnoreCase))
            //{
            //    isSecretary = true;
            //}

            return isSecretary;
        }

        public static bool IsSupervisor(string domainName)
        {
            bool isSecretary = false;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT AccessLevel FROM B2E WHERE EmployeeMaster = @EmployeeMaster;";
                cmd.Parameters.AddWithValue("@EmployeeMaster", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));

                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        if (reader.GetString(0).Equals("SUP", StringComparison.InvariantCultureIgnoreCase))
                            isSecretary = true;
                    }
                }
            }

            if ((KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName) == "S7246847C")
                || (KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName) == "WW0377865"))
            {
                isSecretary = true;
            }

            return isSecretary;
        }

        public static string GetSection(string domainName)
        {
            string section = string.Empty;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT Section FROM B2E WHERE EmployeeMaster = @EmployeeMaster;";
                cmd.Parameters.AddWithValue("@EmployeeMaster", KeppelLand.Helpers.TranslationHelper.GetEmployeeMasterByDomainName(domainName));

                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        section = reader.GetString(0);
                    }
                }
            }

            return section;
        }

        public static List<Employee> GetSupervisorEmployeeList()
        {
            List<Employee> employeeList = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM B2E WHERE AccessLevel = 'SUP' ORDER BY Section, NameLegal;";
                System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds, "Supervisors");

                if (ds.Tables["Supervisors"].Rows.Count > 0)
                {
                    employeeList = ds.Tables["Supervisors"].AsEnumerable().Select(r => new Employee
                    {
                        EmployeeMaster = r.Field<string>("EmployeeMaster"),
                        NameLegal = r.Field<string>("NameLegal"),
                        Section = r.Field<string>("Section")
                    }).ToList();
                }
            }

            return employeeList;
        }

        public static string GetEmailByEmployeeMaster(string employeeMaster)
        {
            string emailAddress = string.Empty;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT Email FROM B2E WHERE EmployeeMaster = @EmployeeMaster;";
                cmd.Parameters.AddWithValue("@EmployeeMaster", employeeMaster);

                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        emailAddress = reader.GetString(0);
                    }
                }
            }

            return emailAddress;
        }

        public static List<string> GetSectionList()
        {
            List<string> sectionList = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT DISTINCT Section FROM B2E WHERE Section <> '' ORDER BY Section;";

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                sectionList = ds.Tables[0].AsEnumerable().Select(r => r.Field<string>("Section")).ToList();
            }

            return sectionList;
        }
    }
}